package com.srv.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.srv.common.DataSourceManager;
import com.srv.common.SummaryUserField;
import com.srv.model.SummaryUser;
import com.srv.persistence.ISummaryUserPersistence;
import com.srv.persistence.PersistenceException;
import com.srv.util.DataBaseUtil;
import com.srv.util.ObjectField;
import com.srv.util.SqlUtil;

public class DBSummaryUserPersistence implements ISummaryUserPersistence {

	private static Log log = LogFactory.getLog(DBSummaryUserPersistence.class);
	
	private static final String INSERT_SUSER_SQL;
	
	private static final String DEFAULT_DATASOURCE_NAME = "srv_def";
	
	private static final String KEY_TABLE_NAME_SUSER = "USER_SUMMARY";
	static{
		StringBuilder builder = new StringBuilder();
		builder.append("INSERT INTO USER_SUMMARY ");
		builder.append("(USER_ID, USER_EMAIL, PASSWORD, SINA_WEIBO_ID, QQ_WEIBO_ID, ISDEL, CREATE_TIME, UPDATE_TIME)");
		builder.append(" VALUES ");
		builder.append("(?,?,?,?,?,?,?,?)");
		INSERT_SUSER_SQL = builder.toString();
	};
	
	public SummaryUser getSummaryUser(long pid) throws PersistenceException{
		Map<ObjectField, Object> keyValues = new HashMap<ObjectField, Object>();
		keyValues.put(SummaryUserField.PID, pid);
		List<SummaryUser> list = querySummaryUser(keyValues);
		
		if(list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	
	public List<SummaryUser> querySummaryUser(Map<ObjectField, Object> keyValues) throws PersistenceException{
		String sql = getSelectSql(keyValues);
		log.debug("The SummaryUser query sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<SummaryUser> list = new ArrayList<SummaryUser>();
		try {
			conn = DataSourceManager.get().getDataSource(DEFAULT_DATASOURCE_NAME).getConnection();
			st = conn.prepareStatement(sql);
			SqlUtil.setStmtParams(st, 1, keyValues);
			rs = st.executeQuery();
			
			while(rs.next()){
				list.add(rsToObject(rs));
			}
		} catch (SQLException e) {
			log.error("query SummaryUser fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return list;
	}

	public SummaryUser insertSummaryUser(SummaryUser user) throws PersistenceException {
		if(user.password_md5 == null){
			throw new PersistenceException("the column \"password\" is must NOT NULL");
		}
		if(user.userID == null){
			throw new PersistenceException("the column \"userid\" is must NOT NULL");
		}
		if(user.createTime == null){
			user.createTime = new Date();
		}
		if(user.updateTime == null){
			user.updateTime = new Date();
		}
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = DataSourceManager.get().getDataSource(DEFAULT_DATASOURCE_NAME).getConnection();
//			conn.setAutoCommit(false);
			st = conn.prepareStatement(INSERT_SUSER_SQL);
			st.setString(1, user.userID);
			st.setString(2, user.userEmail);
			st.setString(3, user.password_md5);
			st.setString(4, user.sinaWeiBoID);
			st.setString(5, user.qqWeiBoID);
			st.setInt(6, user.isDel() ? 1 : 0);
			st.setTimestamp(7, new Timestamp(user.createTime.getTime()));
			st.setTimestamp(8, new Timestamp(user.updateTime.getTime()));
			int flag = st.executeUpdate();
			if(flag <= 0){
				throw new PersistenceException("insert SummaryUser fail");
			}
			//获得自增主键值
			rs = st.executeQuery("select LAST_INSERT_ID()");
			if(rs.next()){
				user.pid = rs.getLong(1);
				//create mapping
				DataSourceManager.get().createDataSourceMapping(user.pid);
				log.debug("insert SummaryUser get PK pid is:" + user.pid);
			}
			return user;
		} catch (SQLException e) {
			log.error("insert SummaryUser fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}

	public boolean updateSummaryUser(long pid, Map<ObjectField, Object> keyValues)throws PersistenceException {
		String sql = getUpdateSql(keyValues);
		log.debug("The SummaryUser update Sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(DEFAULT_DATASOURCE_NAME).getConnection();
			st = conn.prepareStatement(sql);
			int idx = SqlUtil.setStmtParams(st, 1, keyValues);
			st.setLong(idx, pid);
			
			return st.executeUpdate() > 0;
		} catch (SQLException e) {
			log.error("update SummaryUser fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}

	private String getSelectSql(Map<ObjectField, Object> keyValues){
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM ").append(KEY_TABLE_NAME_SUSER);
		
		if (keyValues != null && keyValues.size() > 0) {
			builder.append(" WHERE ").append(SqlUtil.generateWhereCause(keyValues));
        }
		
        return builder.toString();
	}
	
	private String getUpdateSql(Map<ObjectField, Object> keyValues) {
		StringBuilder strBuf = new StringBuilder();

		strBuf.append("UPDATE ").append(KEY_TABLE_NAME_SUSER);

		if (keyValues != null && keyValues.size() > 0) {
			strBuf.append(" SET ").append(SqlUtil.generateSetCause(keyValues));
		}

		strBuf.append(" WHERE PID = ?");

		return strBuf.toString();
	}
	
	private SummaryUser rsToObject(ResultSet rs) throws SQLException{
		SummaryUser user = new SummaryUser();
		user.createTime = new Date(rs.getTimestamp("CREATE_TIME").getTime());
		user.userID = rs.getString("USER_ID");
		user.password_md5 = rs.getString("PASSWORD");
		user.pid = rs.getLong("PID");
		user.qqWeiBoID = rs.getString("QQ_WEIBO_ID");
		user.sinaWeiBoID = rs.getString("SINA_WEIBO_ID");
		user.updateTime =  new Date(rs.getTimestamp("UPDATE_TIME").getTime());
		user.userEmail = rs.getString("USER_EMAIL");;
		user.setIsDel(rs.getInt("ISDEL"));
		
		return user;
	}
	
	public static void main(String[] args) throws PersistenceException {
		ISummaryUserPersistence summaryUserPersistence = new DBSummaryUserPersistence();
		SummaryUser user = new SummaryUser();
		user.userID = "zhaojianle";
		user.password_md5 = "woshinibaba";
		user.createTime = new Date();
//		user.qqWeiBoID = "qq";
//		user.sinaWeiBoID = "sina";
		user.updateTime =  new Date();
		user.userEmail = "enimei";
		user.setIsDel(1);
		user = summaryUserPersistence.insertSummaryUser(user);
		
		log.debug("debug info============:" + user.pid);
	}

}
